with sing_bill as ( --签收扫描+入库扫描，取最新的扫描时间，网点，人员
   select  
      t.*
     ,t1.agent_code         as disp_agent_code                 --派件,代理区编码
     ,t1.agent_name         as disp_agent_name                 --派件,代理区名称
     ,t1.name               as disp_site_name                  --派件,网点名称'
     ,t1.city_id            as disp_city_id                    --派件,城市
     ,t1.city_desc          as disp_city_desc                  --派件,城市name
     ,t1.area_id            as disp_area_id                    --派件,区县
     ,t1.area_desc          as disp_area_desc                  --派件,区县name   
     ,t1.zone_code          as disp_zone_code                  --派件,市场区name   
     ,t1.zone_name          as disp_zone_name                  --派件,市场区name   
     ,to_date(t.disp_time)  as date_time
   from (
           select  
              t.*,   
              row_number()over(partition by t.waybill_no,t.disp_site_code order by t.disp_time asc) as rn
           from (
                        select
                            t.waybill_no,
                            t.scan_site_code as disp_site_code,
                            t.scan_time as disp_time,
                            scan_user as  disp_user_name ,
                            scan_user_code as  disp_user_code 
                        from
                            jms_dwd.dwd_tab_barscan_collect_base_dt t
                        where
                            t.dt between date_add('{{ execution_date | cst_ds }}', -14) and '{{ execution_date | cst_ds }}'  
                            and scan_type = '入库扫描'
                            and to_date(t.scan_time) between date_add('{{ execution_date | cst_ds }}', -7) and '{{ execution_date | cst_ds }}'    

                    union all
                        select
                            t.waybill_no,
                            t.signed_site_code as disp_site_code,
                            t.signed_time as disp_time,
                            t.deliver_user as disp_user_name ,
                            t.send_user_code as disp_user_code 
                        from
                            jms_dwd.dwd_tab_barscan_sign_base_dt t
                        where
                            t.dt between date_add('{{ execution_date | cst_ds }}', -14) and '{{ execution_date | cst_ds }}'  
                            and to_date(t.signed_time) between date_add('{{ execution_date | cst_ds }}', -7) and '{{ execution_date | cst_ds }}'    
            ) t
    ) t 
    left join jms_dim.dim_network_whole_massage t1 on t.disp_site_code=t1.code
    where t.rn =1
)    
,call_record_tmp as (--通话记录表
         select 
               waybill_no       -- 运单编号
              ,count(waybill_no) as call_number  --拨打次数
              ,max(if(connect_status=1,1,0)) as call_flg_1       --已接通本方挂断
              ,max(if(connect_status=2,1,0)) as call_flg_2       --已接通本方挂断
              , 1 as call_flg_3
              ,max(if(connect_status is null or connect_status ='' or connect_status='0',0,1) ) as is_call  --是否接通
              ,max(concat(if(connect_status is null or connect_status ='' or connect_status='0',0,1),'@',start_time)) as call_time
              ,max(intelligent_flag) as intelligent_flag
         from jms_dwd.dwd_sqs_call_record_base_dt 
         where dt between date_add('{{ execution_date | cst_ds }}', -7) and '{{ execution_date | cst_ds }}'
        group by  waybill_no
     )
, tmp_base_table as (
    select         --订单拓展表，取订单来源是紫金山的生鲜件
      t.waybill_id as waybill_no
     ,t2.id        as order_id    --订单id
     ,t2.pick_network_code as pick_site_code
     ,t2.order_source_code
     ,t2.order_source_name
     ,if(t3.waybill_no is not null,1,0) as is_reback      --是否退转件，1是,0否
     ,if(t4.waybill_no is not null,1,0) as is_complaint   --是否投诉，  1是,0否
     ,t5.sign_big
    from jms_dwd.dwd_yl_oms_order_mark_expand_base_dt t
    left join ( select    --订单表，取订单ID
                  t.id
                 ,t.pick_network_code
                 ,t.order_source_code
                 ,t.order_source_name
                 ,t.waybill_id as waybill_no
                from jms_dwd.dwd_yl_oms_oms_order_incre_dt t 
                where t.dt  between date_add('{{ execution_date | cst_ds }}', -15) and '{{ execution_date | cst_ds }}'       
                ) t2 on t.waybill_id=t2.waybill_no
    left join (
                 select 
                   t.waybill_no     
                 from jms_dwd.dwd_tab_reback_transfer_express_base t                  
                    where   to_date(examine_time) between date_add('{{ execution_date | cst_ds }}', -30) and '{{ execution_date | cst_ds }}' 
                 group by t.waybill_no
               )  t3 on t.waybill_id=t3.waybill_no
    
    left join (
                  select 
                     t.waybill_no    --客户投诉表
                  from jms_dm.dm_waybill_customer_complaint_detail_new_dt t
                  where t.dt between date_add('{{ execution_date | cst_ds }}', -7) and '{{ execution_date | cst_ds }}' 
                        and t.is_delete=2     
                  group by t.waybill_no                        

              ) t4 on t.waybill_id=t4.waybill_no
              
    left join (
                  select 
                     t.waybill_no    --送货上门率
                     ,max(sign_big) as sign_big
                  from jms_dm.dm_door_sign_rate_detail_dt t
                  where t.dt = '{{ execution_date | cst_ds }}'                         
                  group by t.waybill_no                        

              ) t5 on t.waybill_id=t5.waybill_no          
              
    where t.dt between date_add('{{ execution_date | cst_ds }}', -14) and '{{ execution_date | cst_ds }}'  
    and  t.field_name = 'SVC-FRESH'
    and  t.order_source_code = 'D67'
    group by t.waybill_id
             ,t2.id
             ,t2.pick_network_code
             ,t2.order_source_code
             ,t2.order_source_name
             ,if(t3.waybill_no is not null,1,0)
             ,if(t4.waybill_no is not null,1,0)
             ,t5.sign_big

)

insert overwrite table jms_dm.dm_sqs_fresh_call_detail_dt partition (dt)
select
  t.waybill_no                                             --运单号
 ,t.order_id                                               --订单id
 ,t1.agent_code         as pick_agent_code                 --取件_代理区编码
 ,t1.agent_name         as pick_agent_name                 --取件_代理区名称
 ,t.pick_site_code                                         --取件_网点名称  
 ,t1.name               as pick_site_name                  --取件_网点名称name
 ,t1.city_id            as pick_city_id                    --取件_城市
 ,t1.city_desc          as pick_city_desc                  --取件_城市name
 ,t1.area_id            as pick_area_id                    --取件_区县
 ,t1.area_desc          as pick_area_desc                  --取件_区县name   
 
 ,t2.disp_agent_code                                       --派件_代理区编码
 ,t2.disp_agent_name                                       --派件_代理区名称
 ,t2.disp_site_code                                        --派件_网点名称
 ,t2.disp_site_name                                        --派件_网点名称
 ,t2.disp_city_id                                          --派件_城市
 ,t2.disp_city_desc                                        --派件_城市name
 ,t2.disp_area_id                                          --派件_区县
 ,t2.disp_area_desc                                        --派件_区县name  
 ,t2.disp_zone_code                                        --派件_市场区name  
 ,t2.disp_zone_name                                        --派件_市场区name  
 ,t.order_source_code                                      --订单来源code   
 ,t.order_source_name                                      --订单来源name   
 ,t.sign_big                                               --签收大类   
 ,t2.disp_time                                             --签收时间   
 ,t2.disp_user_code                                        --派件员   
 ,t2.disp_user_name                                        --派件员code           
 ,t.is_reback                                              --是否退件          
 ,nvl(t3.is_call,0)   as is_call                                  --通话状态1是/0否
 ,t3.intelligent_flag  as intelligent_flag                 --是否智能外乎
 ,t3.call_number                                           --拨打次数
 ,replace(replace(t3.call_time,'1@',''),'0@','') as call_time     --通话时间
 ,nvl(t.is_complaint,0)  is_complaint                                          --是否客诉
 ,nvl(t3.call_flg_1,0)      call_flg_1                                        --已接通本方挂断
 ,nvl(t3.call_flg_2 ,0)      call_flg_2                                      --已接通对方挂断
 ,nvl(t3.call_flg_3,0)      call_flg_3                                       --接通总票数
 ,t2.date_time                                             --date_time
 ,t2.date_time as dt
from tmp_base_table t 
left join sing_bill t2 on t.waybill_no=t2.waybill_no
left join call_record_tmp t3 on t.waybill_no=t3.waybill_no
left join jms_dim.dim_network_whole_massage t1 on t.pick_site_code=t1.code
where t2.date_time between date_add('{{ execution_date | cst_ds }}', -6) and '{{ execution_date | cst_ds }}' 
distribute by abs(hash(waybill_no))%50
;

